package com.egg.service.impl;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.egg.dao.BaseDao;
import com.egg.service.MenuService;
/**
 * @项目名称：egg   
 * @类名称：MenuServiceImpl   
 * @类描述：菜单实现   
 * @创建人：wangchenyang
 * @创建时间：2012-8-31 下午4:28:32   
 * @修改人：
 * @修改时间：   
 * @修改备注：   
 * @version
 */
@Service
public class MenuServiceImpl implements MenuService {

	@Autowired
	BaseDao baseDao;
	@Override
	public List<Map<String, Object>> getAllMenu(Object...obj) {
//		String sql="SELECT T1.ID,T1.MENU_NAME,T1.MENU_URL,T2.MENU_NAME AS P_MENU_NAME,T3.DEPT_NAME FROM EGG_MENU T1 LEFT JOIN EGG_MENU T2 ON (T1.PID=T2.ID),EGG_DEPT T3 WHERE T1.DEPT_ID=T3.ID ORDER BY T1.ID DESC";
		StringBuffer sb=new StringBuffer();
		sb.append(" SELECT T1.ID, T1.MENU_NAME, T1.MENU_URL, T2.MENU_NAME AS P_MENU_NAME"); 
		sb.append(" FROM EGG_MENU T1");  
		sb.append(" LEFT JOIN EGG_MENU T2");  
		sb.append("  ON (T1.PID = T2.ID)");  
		sb.append(" WHERE T1.DEPT_ID=?");
		sb.append(" ORDER BY T1.ID DESC");
		return baseDao.queryForList(sb.toString(),obj);
	}

	@Override
	public List<Map<String, Object>> getOneMenu(Object...obj) {
		String sql="SELECT T1.ID,T1.MENU_NAME,T1.MENU_URL,T1.DEPT_ID,T1.PID AS P_ID,T2.MENU_NAME AS P_MENU_NAME FROM EGG_MENU T1 LEFT JOIN EGG_MENU T2 ON T1.PID=T2.ID WHERE  T1.ID=?";
		return baseDao.queryForList(sql, obj);
	}

	@Override
	public void addMenu(Object...obj) {
		String sql="INSERT INTO EGG_MENU(ID,MENU_NAME,MENU_URL,DEPT_ID,PID,CREATE_TIME) VALUES(SEQ_EGG_MENU.NEXTVAL,?,?,?,?,?)";
		baseDao.update(sql, obj);
	}

	@Override
	public void updateMenu(Object...obj) {
		String sql="UPDATE EGG_MENU SET MENU_NAME=?,MENU_URL=?,DEPT_ID=?,PID=? WHERE ID=?";
		baseDao.update(sql, obj);
	}

	@Override
	public void deleteMenu(Object...obj) {
		String sql="DELETE FROM EGG_MENU WHERE ID=?";
		baseDao.update(sql, obj);
	}

	@Override
	public List<Map<String, Object>> getMenuTreeByDeptId(Object...obj) {
		String sql="SELECT ID,PID,MENU_NAME FROM EGG_MENU WHERE DEPT_ID=?";
		return baseDao.queryForList(sql,obj);
	}

	@Transactional
	@Override
	public void assignPower(final String roleId,String menuId) {
		//删除之前关联的数据
		String sql1="DELETE FROM EGG_MENU_ROLE WHERE ROLE_ID=?";
		baseDao.update(sql1, roleId);
		String sql2="INSERT INTO EGG_MENU_ROLE(ID,MENU_ID,ROLE_ID) VALUES(SEQ_EGG_MR.NEXTVAL,?,?)";
		if(menuId!=null&&!"".equals(menuId)){
			//去掉最后一个分号
			menuId=menuId.substring(0,menuId.length()-1);
		}
		final String[] menuArray=menuId.split(",");
		baseDao.getJdbcTemplate().batchUpdate(sql2, new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setString(1, menuArray[i].toString());
				ps.setString(2, roleId);
			}
			
			@Override
			public int getBatchSize() {
				return menuArray.length;
			}
		});
	}

	@Override
	public List<Map<String, Object>> getAuthMenu(Object... obj) {
		StringBuffer sb=new StringBuffer();
		sb.append("SELECT EM.ID, EM.PID, EM.MENU_NAME, EM.MENU_URL"); 
		sb.append("  FROM EGG_USER_ROLE EUR, EGG_MENU_ROLE EMR, EGG_MENU EM"); 
		sb.append(" WHERE EUR.ROLE_ID = EMR.ROLE_ID"); 
		sb.append("   AND EMR.MENU_ID = EM.ID"); 
		sb.append("   AND EUR.USER_ID =?");
//		String sql="SELECT EM.ID,EM.MENU_NAME,EM.MENU_URL,EM.PID FROM EGG_USER_ROLE EUR,EGG_MENU_ROLE EMR,EGG_MENU EM WHERE EUR.ROLE_ID=EMR.ROLE_ID AND EUR.USER_ID=? AND EMR.MENU_ID=EM.ID";
		return baseDao.queryForList(sb.toString(), obj);
	}

	@Override
	public List<Map<String, Object>> getMenuByRoleId(Object... obj) {
		String sql="SELECT * FROM EGG_MENU_ROLE WHERE ROLE_ID=?";
		return baseDao.queryForList(sql, obj);
	}

}
